﻿using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ServiceBroker
{
    public class sqlDependency
    {
        private readonly ILogger<sqlDependency> _logger;
        public static string conn = "Data Source=127.0.0.1;Initial Catalog=Test;User ID=sa;Password=123";
        public sqlDependency(ILogger<sqlDependency> logger)
        {
            _logger = logger;
        }
        public void init()
        {
            _logger.LogInformation("启动sqlDependency");
            SqlDependency.Start(conn);
        }
        public List<Tuple<string, string>> Update()
        {
            SqlDependency.Start(conn);
            using (var connection = new SqlConnection(conn))
            {
                
                var sql = "select name,vl from  dbo.test";
                List<Tuple<string, string>> tupList = new List<Tuple<string, string>>();
                using (var command = new SqlCommand(sql, connection))
                {
                    connection.Open();
                    SqlDependency dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if (string.IsNullOrEmpty(reader["name"].ToString()) || string.IsNullOrEmpty(reader["vl"].ToString()))
                            {
                                Console.WriteLine(reader["name"].ToString(), reader["vl"].ToString());
                                Tuple<string, string> tuple = new Tuple<string, string>(reader["name"].ToString(), reader["vl"].ToString());
                                tupList.Add(tuple);
                            }

                        }
                        _logger.LogInformation((dependency.HasChanges).ToString());
                        reader.Close();
                    }
                }
                return tupList;
            }

        }
        public void OnDependencyChange(object sender, SqlNotificationEventArgs e)
        {
            _logger.LogInformation("触发sqlDependency");
            Update();
        }
        public void Termination()
        {
            _logger.LogInformation("停止sqlDependency");
            SqlDependency.Stop(conn);
        }
    }
}
